package com.example.demo.service;

import com.example.demo.data.SxConfig;
import com.example.demo.vo.Pallroutecomb;
import lombok.RequiredArgsConstructor;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * Created by ltian on 2020/1/18.
 */
@Service
@RequiredArgsConstructor
public class DataQueryService {

    private final SxConfig config;

    @Autowired
    @Qualifier("secondaryJdbcTemplate")
    protected JdbcTemplate jdbcTemplate;


    public List<Pallroutecomb> queryPallroutecombList(String enStatsionId, String exStationId) {
        return jdbcTemplate.query("SELECT * FROM " + config.getPallroutecomb() + " WHERE enstationid = ? AND exstationid = ?",
                new Object[]{enStatsionId, exStationId}, new BeanPropertyRowMapper<>(Pallroutecomb.class));
    }


    public String queryEnLandId(int type, String stationHex) {
        String sql = "select t.id from  (select * from " + config.getBasicInfoHex() + " where type=? and hex= ? order by verno desc)t where rownum =1";
        List<String> ids = jdbcTemplate.queryForList(sql, new Object[]{type, stationHex}, String.class);
        if (ids.size() > 0) {
            return ids.get(0);
        }
        return null;
    }


    public String queryLaneId(String stationHex) {
        String sql = "select t.id from  (select * from " + config.getBasicInfoHex() + " where type=2 and hex= ? order by verno desc)t where rownum =1";
        List<String> ids = jdbcTemplate.queryForList(sql, new Object[]{stationHex}, String.class);
        if (ids.size() > 0) {
            return ids.get(0);
        }
        return null;
    }


    // 通过收费站编号检索出口车道编号
    public String queryExLandIdByStationNo(Long stationNo) {
        if (stationNo == null) {
            return null;
        }
        String sql = "select t.id from  (select * from " + config.getBasicInfoHex() + " where type=2 and spare3 = ? order by verno desc, id DESC) t where rownum =1";
        List<String> ids = jdbcTemplate.queryForList(sql, new Object[]{stationNo}, String.class);
        if (ids.size() > 0) {
            return ids.get(0);
        }
        return null;
    }

    public String queryStationId(String stationHex) {
        String sql = "select t.id from  (select * from " + config.getBasicInfoHex() + " where type=1 and hex= ? order by verno desc)t where rownum =1";
        List<String> ids = jdbcTemplate.queryForList(sql, new Object[]{stationHex}, String.class);
        if (ids.size() > 0) {
            return ids.get(0);
        }
        return null;
    }


    public String queryStationName(String stationHex) {
        String sql = "select t.name from  (select * from " + config.getBasicInfoHex() + " where type=1 and hex= ? order by verno desc)t where rownum =1";
        List<String> ids = jdbcTemplate.queryForList(sql, new Object[]{stationHex}, String.class);
        if (ids.size() > 0) {
            return ids.get(0);
        }
        return null;
    }


    // 同时取到车道和hex
    public Pair<String, String> queryLandIdAndHex(String stationHex) {
        String sql = "select t.id, t.hex from (select * from " + config.getBasicInfoHex() + " where type=2 and substr(hex,1,8) = ? order by verno desc, hex desc) t where rownum =1";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, new Object[]{stationHex});
        if (maps.size() > 0) {
            Map<String, Object> map = maps.get(0);
            return Pair.of((String) map.get("ID"), (String) map.get("HEX"));
        }
        return null;
    }


}
